/*
 * $RCSfile: Dialect.java,v $
 * $Revision: 1.1 $
 * $Date: 2009-03-01 $
 *
 * Copyright (C) 2008 Skin, Inc. All rights reserved.
 *
 * This software is the proprietary information of Skin, Inc.
 * Use is subject to license terms.
 */
package com.skin.webcat.database.dialect;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.skin.webcat.database.Column;
import com.skin.webcat.database.Table;
import com.skin.webcat.util.Jdbc;

/**
 * <p>Title: Dialect</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2006</p>
 * @author xuesong.net
 * @version 1.0
 */
public abstract class AbstractDialect implements Dialect {
    protected Connection connection;
    private static final Logger logger = LoggerFactory.getLogger(AbstractDialect.class);

    /**
     * @return Connection
     */
    @Override
    public Connection getConnection() {
        return this.connection;
    }

    /**
     * @param connection
     */
    @Override
    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    /**
     * @param connection
     * @return String
     * @throws SQLException
     */
    public String getUserName(Connection connection) throws SQLException {
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        return databaseMetaData.getUserName();
    }

    /**
     * @param catalog
     * @param schema
     * @param pattern
     * @param types
     * @param flag
     * @return List<Table>
     * @throws SQLException
     */
    @Override
    public List<Table> getTableList(String catalog, String schema, String pattern, String[] types, boolean flag) throws SQLException {
        ResultSet resultSet = null;
        List<Table> tableList = new ArrayList<Table>();
        logger.debug("catalog: {}, schema: {}, pattern: {}", catalog, schema, pattern);

        try {
            DatabaseMetaData metaData = this.connection.getMetaData();
            resultSet = metaData.getTables(catalog, schema, pattern, types);

            while(resultSet.next()) {
                String tableName = resultSet.getString("TABLE_NAME");
                String tableType = resultSet.getString("TABLE_TYPE");
                String remarks = resultSet.getString("REMARKS");
                String className = this.camel(tableName);
                String variableName = Character.toLowerCase(className.charAt(0)) + className.substring(1);
                logger.debug("tableName: {}, remarks: {}", tableName, remarks);

                Table table = new Table();
                table.setTableCode(tableName);
                table.setTableName(tableName);
                table.setTableType(tableType);
                table.setRemarks(remarks);
                table.setQueryName(tableName);
                table.setClassName(className);
                table.setVariableName(variableName);
                tableList.add(table);
            }

            if(flag) {
                for(Table table : tableList) {
                    String tableName = table.getTableName();
                    List<String> primaryKeys = this.getPrimaryKeys(metaData, catalog, schema, tableName);
                    List<Column> columns = this.getColumns(metaData, schema, tableName);
                    table.setColumns(columns);

                    for(Column column : columns) {
                        String columnName = column.getColumnName();

                        if(primaryKeys.contains(columnName)) {
                            column.setPrimaryKey(true);
                        }
                        else {
                            column.setPrimaryKey(false);
                        }
                    }
                }
            }
        }
        finally {
            Jdbc.close(resultSet);
        }
        return tableList;
    }

    /**
     * @param tableName
     * @return Table
     * @throws SQLException
     */
    @Override
    public Table getTable(String tableName) throws SQLException {
        return this.getTable(null, null, tableName, new String[]{"TABLE"});
    }

    /**
     * @param catalog
     * @param schema
     * @param tableName
     * @return Table
     * @throws SQLException
     */
    @Override
    public Table getTable(String catalog, String schema, String tableName) throws SQLException {
        return this.getTable(catalog, schema, tableName, new String[]{"TABLE"});
    }

    /**
     * @param catalog
     * @param schema
     * @param tableName
     * @param types
     * @return Table
     * @throws SQLException
     */
    @Override
    public Table getTable(String catalog, String schema, String tableName, String[] types) throws SQLException {
        List<Table> list = this.getTableList(catalog, schema, tableName, types, true);

        if(list != null && !list.isEmpty()) {
            return list.get(0);
        }
        else {
            return null;
        }
    }

    /**
     * @param metaData
     * @return Table
     * @throws SQLException
     */
    @Override
    public Table getTable(ResultSetMetaData metaData) throws SQLException {
        Table table = new Table();
        int columnCount = metaData.getColumnCount();

        for(int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            int dataType = metaData.getColumnType(i);
            String typeName = metaData.getColumnTypeName(i);
            int columnSize = metaData.getColumnDisplaySize(i);
            int decimalDigits = metaData.getPrecision(i);
            String variable = java.beans.Introspector.decapitalize(this.camel(columnName));

            if("ID".equals(variable)) {
            }
            else {
                variable = Character.toLowerCase(variable.charAt(0)) + variable.substring(1);
            }

            Column column = new Column();
            column.setTable(null);
            column.setColumnCode(columnName);
            column.setColumnName(columnName);
            column.setDataType(dataType);
            column.setTypeName(typeName);
            column.setColumnSize(columnSize);
            column.setDecimalDigits(decimalDigits);
            column.setNullable(true);
            column.setRemarks("");
            column.setColumnDef("");
            column.setPrimaryKey(false);
            column.setVariableName(variable);
            column.setMethodSetter("set" + this.camel(columnName));
            column.setMethodGetter("get" + this.camel(columnName));
            this.process(column);
            table.add(column);
        }
        return table;
    }

    /**
     * @param metaData
     * @param catalog
     * @param schema
     * @param tableName
     * @return List<String>
     * @throws SQLException
     */
    public List<String> getPrimaryKeys(DatabaseMetaData metaData, String catalog, String schema, String tableName) throws SQLException {
        ResultSet resultSet = null;
        List<String> primaryKeys = new ArrayList<String>();

        try {
            resultSet = metaData.getPrimaryKeys(catalog, schema, tableName);

            while(resultSet.next()) {
                String columnName = resultSet.getString("COLUMN_NAME");
                primaryKeys.add(columnName);
            }
        }
        finally {
            Jdbc.close(resultSet);
        }
        return primaryKeys;
    }

    /**
     * @param metaData
     * @param schema
     * @param tableName
     * @return Column
     * @throws SQLException 
     */
    public List<Column> getColumns(DatabaseMetaData metaData, String schema, String tableName) throws SQLException {
        ResultSet resultSet = null;
        List<Column> columns = new ArrayList<Column>();

        try {
            resultSet = metaData.getColumns(null, schema, tableName, "%");

            while(resultSet.next()) {
                String columnName = resultSet.getString("COLUMN_NAME");
                String variable = java.beans.Introspector.decapitalize(this.camel(columnName));

                if("ID".equals(variable)) {
                }
                else {
                    variable = Character.toLowerCase(variable.charAt(0)) + variable.substring(1);
                }

                int dataType = resultSet.getInt("DATA_TYPE");
                String typeName = resultSet.getString("TYPE_NAME");
                boolean autoIncrement = getBoolean(resultSet, "IS_AUTOINCREMENT");
                int columnSize = resultSet.getInt("COLUMN_SIZE");
                int decimalDigits = resultSet.getInt("DECIMAL_DIGITS");
                String columnDef = resultSet.getString("COLUMN_DEF");
                String columnRemark = resultSet.getString("REMARKS");
                boolean nullable = resultSet.getBoolean("NULLABLE");

                logger.debug("column: {}, size: {}", columnName, columnSize);
                Column column = new Column();
                column.setColumnCode(columnName);
                column.setColumnName(columnName);
                column.setDataType(dataType);
                column.setTypeName(typeName);
                column.setAutoIncrement(autoIncrement);
                column.setColumnSize(columnSize);
                column.setDecimalDigits(decimalDigits);
                column.setNullable(nullable);
                column.setRemarks(columnRemark);
                column.setColumnDef(columnDef);
                column.setVariableName(variable);
                column.setMethodSetter("set" + this.camel(columnName));
                column.setMethodGetter("get" + this.camel(columnName));
                this.process(column);
                columns.add(column);
            }
        }
        finally {
            Jdbc.close(resultSet);
        }
        return columns;
    }

    /**
     * @param resultSet
     * @param name
     * @return boolean
     * @throws SQLException 
     */
    private boolean getBoolean(ResultSet resultSet, String name) throws SQLException {
        String value = resultSet.getString(name);

        if(value == null) {
            return false;
        }
        value = value.toLowerCase();
        return (value.equals("1") || value.equals("y") || value.equals("yes") || value.equals("true"));
    }
    
    /**
     * @param column
     * @return String
     */
    @Override
    public String convert(Column column) {
        int dataType = column.getDataType();

        switch(dataType) {
            case java.sql.Types.BIT: {
                return "boolean";
            }
            case java.sql.Types.TINYINT:
            case java.sql.Types.SMALLINT:
            case java.sql.Types.INTEGER: {
                return "int";
            }
            case java.sql.Types.BIGINT: {
                return "long";
            }
            case java.sql.Types.FLOAT: {
                return "float";
            }
            case java.sql.Types.DOUBLE: {
                return "double";
            }
            case java.sql.Types.NUMERIC: {
                return "java.math.BigDecimal";
            }
            case java.sql.Types.DECIMAL: {
                return "java.math.BigDecimal";
            }
            case java.sql.Types.REAL: {
                return "float";
            }
            case java.sql.Types.CHAR:
            case java.sql.Types.VARCHAR:
            case java.sql.Types.LONGVARCHAR: {
                return "String";
            }
            case java.sql.Types.DATE:
            case java.sql.Types.TIME:
            case java.sql.Types.TIMESTAMP: {
                return "java.util.Date";
            }
            case java.sql.Types.BINARY: {
                return "byte[]";
            }
            case java.sql.Types.VARBINARY: {
                return "byte[]";
            }
            case java.sql.Types.LONGVARBINARY: {
                return "byte[]";
            }
            case java.sql.Types.NULL: {
                return "java.lang.Void";
            }
            case java.sql.Types.OTHER: {
                return "Object";
            }
            case java.sql.Types.JAVA_OBJECT: {
                return "Object";
            }
            case java.sql.Types.DISTINCT: {
                return "Object";
            }
            case java.sql.Types.STRUCT: {
                return "Object";
            }
            case java.sql.Types.ARRAY: {
                return "Object";
            }
            case java.sql.Types.BLOB: {
                return "java.io.InputStream";
            }
            case java.sql.Types.CLOB: {
                return "java.io.Reader";
            }
            case java.sql.Types.REF: {
                return "Object";
            }
            case java.sql.Types.DATALINK: {
                return "Object";
            }
            case java.sql.Types.BOOLEAN: {
                return "boolean";
            }
            case java.sql.Types.ROWID: {
                return "Object";
            }
            case java.sql.Types.NCHAR: {
                return "String";
            }
            case java.sql.Types.NVARCHAR: {
                return "String";
            }
            case java.sql.Types.LONGNVARCHAR: {
                return "String";
            }
            case java.sql.Types.NCLOB: {
                return "String";
            }
            case java.sql.Types.SQLXML: {
                return "String";
            }
            default: {
                return "Object";
            }
        }
    }

    /**
     * @param name
     * @return String
     */
    protected String camel(String name) {
        if(null == name || name.trim().length() < 1) {
            return "";
        }

        String[] subs = name.split("_");
        StringBuilder buffer = new StringBuilder();

        if(name.startsWith("_")) {
            buffer.append("_");
        }

        if(subs.length == 1) {
            String s = subs[0];

            if("ID".equals(s)) {
                buffer.append("Id");
            }
            else if(s.toUpperCase().equals(s)) {
                buffer.append(Character.toUpperCase(s.charAt(0)));
                buffer.append(s.substring(1).toLowerCase());
            }
            else {
                buffer.append(Character.toUpperCase(s.charAt(0))).append(s.substring(1));
            }
        }
        else {
            for(String s : subs) {
                if(s.length() > 0) {
                    if("ID".equals(s)) {
                        buffer.append(s);
                    }
                    else if(s.toUpperCase().equals(s)) {
                        buffer.append(Character.toUpperCase(s.charAt(0)));
                        buffer.append(s.substring(1).toLowerCase());
                    }
                    else {
                        buffer.append(Character.toUpperCase(s.charAt(0))).append(s.substring(1));
                    }
                }
            }
        }

        if(name.endsWith("_")) {
            buffer.append("_");
        }
        return buffer.toString();
    }
}
